package com.F04.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F04.actionForm.F0409ActionForm;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.util.CommonTools;

public class F0409Dao extends DataOperations {

	Logger log = Logger.getLogger(F0409Dao.class);

	public F0409Dao() {

	}

	public F0409Dao(Connection conn) {
		super(conn);
	}

	public List[] select(F0409ActionForm ActionForm) {
		List weekList = new ArrayList(); // 存放周数
		List rtnList = new ArrayList(); // 存放查询结果
		List[] list = new ArrayList[2];
		Map map = null;

		StringBuffer sqlWeek = new StringBuffer();
		StringBuffer sqlStat = new StringBuffer();

		String ManufacturingOrder = CommonTools.null2String(ActionForm
				.getManufacturingOrder());
		String rtn[] = new String[53];

		sqlWeek
				.append("select distinct (case when adyh.FUN_GetNumOfWeek(p.[start_date]) - 1 > ");
		sqlWeek
				.append("							adyh.FUN_GetNumOfWeek(DATEADD(day, adyh.FUN_GetMaxDay(delivery_time_s1, delivery_time_s2, delivery_time_s3,delivery_time_s4)+7, GETDATE())) ");
		sqlWeek
				.append("  							then adyh.FUN_GetNumOfWeek(p.[start_date]) -1");
		sqlWeek
				.append("  							else adyh.FUN_GetNumOfWeek(DATEADD(day, adyh.FUN_GetMaxDay(delivery_time_s1,delivery_time_s2, delivery_time_s3, delivery_time_s4)+7, GETDATE())) end) numOfWeek,");
		sqlWeek.append("         adyh.FUN_GetNumOfWeek(getdate()) sysdate ");
		sqlWeek
				.append("        from t_materials m,pc_materials_supplier ms,t_not_standard_products nsp,t_not_standard_detail nsd,pd_planning p ");
		sqlWeek.append("        where m.code = ms.material_code and");
		sqlWeek.append("              nsp.code = nsd.standard_code and");
		sqlWeek.append("              nsd.material_code = m.code and");
		sqlWeek
				.append("              p.manufacturing_order = nsp.manufacturing_order and");
		sqlWeek.append("              p.purchase_flag = '1'");
		int i = 0;
		int j = 0;
		int week[] = new int[53];

		initWk(ActionForm);

		// 查询条件
		if (!"".equals(ManufacturingOrder)) {
			sqlWeek.append(" and p.manufacturing_order = '"
					+ ManufacturingOrder + "'");
		}

		ResultSet rest = null;
		try {
			rest = super.executeQuery(sqlWeek.toString());
			while (rest.next()) {
				map = new HashMap();
				int numOfWeek = rest.getInt("numOfWeek");
				map.put("numOfWeek", String.valueOf(numOfWeek));
				weekList.add(map);

				this.setWk(ActionForm, numOfWeek);
				if (ActionForm.getCurrentWeek() + "" == null
						|| "0".equals(String.valueOf(ActionForm.getCurrentWeek()))) {
					ActionForm.setCurrentWeek(rest.getInt("sysdate"));
				}
				rtn[j] = "wk" + numOfWeek;
				j++;
			}

			// 未检索到数据
			if (map == null) {
				ActionForm.setErrorFlag("2");
				ActionForm.setCurrentWeek(0);
				return null;
			}

			sqlStat
					.append("select numOfWeek,b.material_code,b.material_name,mq.quantity stock,b.qty qty,b.manufacturing_order,b.flag ");
			sqlStat
					.append(" from (select case  when adyh.FUN_GetNumOfWeek(p.[start_date]) - 1 > adyh.FUN_GetNumOfWeek(DATEADD(day, adyh.FUN_GetMaxDay(delivery_time_s1, delivery_time_s2,delivery_time_s3, delivery_time_s4)+7, GETDATE())) then adyh.FUN_GetNumOfWeek(p.[start_date]) - 1 else adyh.FUN_GetNumOfWeek(DATEADD(day, adyh.FUN_GetMaxDay(delivery_time_s1,delivery_time_s2, delivery_time_s3, delivery_time_s4)+7, GETDATE())) end numOfWeek, ");
			sqlStat
					.append("			  case  when adyh.FUN_GetNumOfWeek(p.[start_date]) - 1 < adyh.FUN_GetNumOfWeek(DATEADD(day, adyh.FUN_GetMaxDay(delivery_time_s1, delivery_time_s2,delivery_time_s3, delivery_time_s4)+7, GETDATE())) then 1 else 0 end flag, ");
			sqlStat
					.append("       nsd.material_code material_code,m.[name] material_name,sum(nsd.quantity * p.group_qty) qty,p.manufacturing_order ");
			sqlStat
					.append("from t_materials m,pc_materials_supplier ms,t_not_standard_products nsp,t_not_standard_detail nsd,pd_planning p ");
			sqlStat.append("where nsp.code = nsd.standard_code and ");
			sqlStat.append("      nsd.material_code = ms.material_code and ");
			sqlStat.append("      m.code = nsd.material_code and ");
			sqlStat
					.append("      nsp.manufacturing_order = p.manufacturing_order and ");
			sqlStat.append("      p.purchase_flag = '1'");
			if (!"".equals(ManufacturingOrder)) {
				sqlStat.append("  and p.manufacturing_order = '"
						+ ManufacturingOrder + "' ");
			}
			sqlStat
					.append(" group by p.manufacturing_order,nsd.material_code,m.name,case when adyh.FUN_GetNumOfWeek(p.[start_date]) - 1 > adyh.FUN_GetNumOfWeek(DATEADD(day,adyh.FUN_GetMaxDay(delivery_time_s1, delivery_time_s2,delivery_time_s3, delivery_time_s4)+7, GETDATE())) then adyh.FUN_GetNumOfWeek(p.[start_date]) - 1 else adyh.FUN_GetNumOfWeek(DATEADD(day, adyh.FUN_GetMaxDay(delivery_time_s1, delivery_time_s2, delivery_time_s3,delivery_time_s4)+7, GETDATE())) end,case  when adyh.FUN_GetNumOfWeek(p.[start_date]) - 1 < adyh.FUN_GetNumOfWeek(DATEADD(day, adyh.FUN_GetMaxDay(delivery_time_s1, delivery_time_s2,delivery_time_s3, delivery_time_s4)+7, GETDATE())) then 1 else 0 end ) b,pc_material_cost mq where b.material_code = mq.material_code ");
			sqlStat.append("order by b.manufacturing_order,b.material_code");
			log.info(sqlStat.toString());
			rest = super.executeQuery(sqlStat.toString());
			i = 1;
			String materialCode = "";
			String manufacturingOrder = "";
			int qty = 0;
			while (rest.next()) {
				// 相同工作号下不同组的原材料在不同周到达
				if (materialCode.equals(CommonTools.null2String(rest
						.getString("material_code")))
						&& manufacturingOrder.equals(CommonTools
								.null2String(rest
										.getString("manufacturing_order")))) {
					for (int n = 0; n < rtn.length; n++) {
						if (rtn[n] == null) {
							break;
						}
						int numOfWeek = rest.getInt("numOfWeek");
						if (rtn[n].equals("wk" + numOfWeek)) {
							qty = rest.getInt("qty");
							map.put(rtn[n], String.valueOf(qty));
							break;
						}
					}
				} else {
					materialCode = CommonTools.null2String(rest
							.getString("material_code"));
					manufacturingOrder = CommonTools.null2String(rest
							.getString("manufacturing_order"));
					map = new HashMap();
					map.put("materialCode", materialCode);
					map.put("materialName", CommonTools.null2String(rest
							.getString("material_name")));
					map.put("stock", String.valueOf(rest.getInt("stock")));
					map.put("manufacturingOrder", manufacturingOrder);
					map.put("flag", CommonTools.null2String(rest
							.getString("flag")));
					j = 0;
					qty = 0;
					for (int n = 0; n < rtn.length; n++) {
						if (rtn[n] == null) {
							break;
						}
						int numOfWeek = rest.getInt("numOfWeek");
						if (rtn[n].equals("wk" + numOfWeek)) {
							qty = rest.getInt("qty");
						} else {
							qty = 0;
						}
						map.put(rtn[n], String.valueOf(qty));
					}
//					map.put("id", i + "");
//					i++;
					rtnList.add(map);
				}
			}
			list[0] = weekList;
			list[1] = rtnList;
		} catch (Exception e) {
			e.printStackTrace();
			ActionForm.setErrorFlag("3");
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	public void initWk(F0409ActionForm ActionForm) {

		ActionForm.setWk1("0");
		ActionForm.setWk2("0");
		ActionForm.setWk3("0");
		ActionForm.setWk4("0");
		ActionForm.setWk5("0");
		ActionForm.setWk6("0");
		ActionForm.setWk7("0");
		ActionForm.setWk8("0");
		ActionForm.setWk9("0");
		ActionForm.setWk10("0");
		ActionForm.setWk11("0");
		ActionForm.setWk12("0");
		ActionForm.setWk13("0");
		ActionForm.setWk14("0");
		ActionForm.setWk15("0");
		ActionForm.setWk16("0");
		ActionForm.setWk17("0");
		ActionForm.setWk18("0");
		ActionForm.setWk19("0");
		ActionForm.setWk20("0");
		ActionForm.setWk21("0");
		ActionForm.setWk22("0");
		ActionForm.setWk23("0");
		ActionForm.setWk24("0");
		ActionForm.setWk25("0");
		ActionForm.setWk26("0");
		ActionForm.setWk27("0");
		ActionForm.setWk28("0");
		ActionForm.setWk29("0");
		ActionForm.setWk30("0");
		ActionForm.setWk31("0");
		ActionForm.setWk32("0");
		ActionForm.setWk33("0");
		ActionForm.setWk34("0");
		ActionForm.setWk35("0");
		ActionForm.setWk36("0");
		ActionForm.setWk37("0");
		ActionForm.setWk38("0");
		ActionForm.setWk39("0");
		ActionForm.setWk40("0");
		ActionForm.setWk41("0");
		ActionForm.setWk42("0");
		ActionForm.setWk43("0");
		ActionForm.setWk44("0");
		ActionForm.setWk45("0");
		ActionForm.setWk46("0");
		ActionForm.setWk47("0");
		ActionForm.setWk48("0");
		ActionForm.setWk49("0");
		ActionForm.setWk50("0");
		ActionForm.setWk51("0");
		ActionForm.setWk52("0");
		ActionForm.setWk53("0");
		ActionForm.setWk54("0");
	}

	public void setWk(F0409ActionForm ActionForm, int i) throws SelectException {
		if (i == 1) {
			ActionForm.setWk1("1");
		}
		if (i == 2) {
			ActionForm.setWk2("1");
		}
		if (i == 3) {
			ActionForm.setWk3("1");
		}
		if (i == 4) {
			ActionForm.setWk4("1");
		}
		if (i == 5) {
			ActionForm.setWk5("1");
		}
		if (i == 6) {
			ActionForm.setWk6("1");
		}
		if (i == 7) {
			ActionForm.setWk7("1");
		}
		if (i == 8) {
			ActionForm.setWk8("1");
		}
		if (i == 9) {
			ActionForm.setWk9("1");
		}
		if (i == 10) {
			ActionForm.setWk10("1");
		}
		if (i == 11) {
			ActionForm.setWk11("1");
		}
		if (i == 12) {
			ActionForm.setWk12("1");
		}
		if (i == 13) {
			ActionForm.setWk13("1");
		}
		if (i == 14) {
			ActionForm.setWk14("1");
		}
		if (i == 15) {
			ActionForm.setWk15("1");
		}
		if (i == 16) {
			ActionForm.setWk16("1");
		}
		if (i == 17) {
			ActionForm.setWk17("1");
		}
		if (i == 18) {
			ActionForm.setWk18("1");
		}
		if (i == 19) {
			ActionForm.setWk19("1");
		}
		if (i == 20) {
			ActionForm.setWk20("1");
		}
		if (i == 21) {
			ActionForm.setWk21("1");
		}
		if (i == 22) {
			ActionForm.setWk22("1");
		}
		if (i == 23) {
			ActionForm.setWk23("1");
		}
		if (i == 24) {
			ActionForm.setWk24("1");
		}
		if (i == 25) {
			ActionForm.setWk25("1");
		}
		if (i == 26) {
			ActionForm.setWk26("1");
		}
		if (i == 27) {
			ActionForm.setWk27("1");
		}
		if (i == 28) {
			ActionForm.setWk28("1");
		}
		if (i == 29) {
			ActionForm.setWk29("1");
		}
		if (i == 30) {
			ActionForm.setWk30("1");
		}
		if (i == 31) {
			ActionForm.setWk31("1");
		}
		if (i == 32) {
			ActionForm.setWk32("1");
		}
		if (i == 33) {
			ActionForm.setWk33("1");
		}
		if (i == 34) {
			ActionForm.setWk34("1");
		}
		if (i == 35) {
			ActionForm.setWk35("1");
		}
		if (i == 36) {
			ActionForm.setWk36("1");
		}
		if (i == 37) {
			ActionForm.setWk37("1");
		}
		if (i == 38) {
			ActionForm.setWk38("1");
		}
		if (i == 39) {
			ActionForm.setWk39("1");
		}
		if (i == 40) {
			ActionForm.setWk40("1");
		}
		if (i == 41) {
			ActionForm.setWk41("1");
		}
		if (i == 42) {
			ActionForm.setWk42("1");
		}
		if (i == 43) {
			ActionForm.setWk43("1");
		}
		if (i == 44) {
			ActionForm.setWk44("1");
		}
		if (i == 45) {
			ActionForm.setWk45("1");
		}
		if (i == 46) {
			ActionForm.setWk46("1");
		}
		if (i == 47) {
			ActionForm.setWk47("1");
		}
		if (i == 48) {
			ActionForm.setWk48("1");
		}
		if (i == 49) {
			ActionForm.setWk49("1");
		}
		if (i == 50) {
			ActionForm.setWk50("1");
		}
		if (i == 51) {
			ActionForm.setWk51("1");
		}
		if (i == 52) {
			ActionForm.setWk52("1");
		}
		if (i == 53) {
			ActionForm.setWk53("1");
		}
		if (i == 54) {
			ActionForm.setWk54("1");
		}
	}
}
